import java.sql.*;
import java.io.*;
import oracle.jdbc.pool.OracleDataSource;
import java.util.ArrayList;
import java.util.Date.*;
import java.text.*;

public class dbapi{

	// Oracle connection
	public static Connection conn;
	// Boolean for checking if dbapi is connected to Oracle
	public static boolean connected;
	// String for sending error messages to servlet
	public static String msg;
	
	// dbapi constructor 
	public static void dbapi()
	{
		connected = false;
	}

	// Connect to Oracle
	public static boolean connect()
	{
		OracleDataSource ods;
		try {
			ods = new OracleDataSource();
			String connectionString = "jdbc:oracle:thin:project/project@localhost:1521:orcl";
			ods.setURL(connectionString);
			conn = ods.getConnection();
		} catch (SQLException e) {
			msg = stack2string(e);
			connected = false;
			return false;
		}
		connected = true;
		return true;
	}
	
	// Return the count of houses in the database
	public static int getHomeCount()
	{
		int count = -1;
try
		{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"select count(*) from homes");
			rset.next();
			count = rset.getInt(1);
			stmt.close();
		}catch(SQLException e){
			msg = stack2string(e);
		}
	
		return count;
	}

	// Get a single home with home ID as a parameter
	public static home getHome(int id)
	{
		// Create new home instance
		home h = new home();
		try{
			// Initiate statment
			Statement stmt = conn.createStatement();
			// Execute Query
			ResultSet rset = stmt.executeQuery(
					"Select * from homes where HOMEID = "+Integer.toString(id));
			while(rset.next())
			{
				// Put data into an instance of home
				h.id =  rset.getInt(1);
				h.address = rset.getString(2);
				h.city = rset.getString(3);
				h.state = rset.getString(4);
				h.zipcode = rset.getInt(5);
				h.sqft = rset.getInt(6);
				h.numFloors = rset.getInt(7);
				h.numBedrooms = rset.getInt(8);
				h.numBathrooms = rset.getInt(9);
				h.landSize = rset.getInt(10);
				h.year = rset.getInt(11);
				h.homeClass = rset.getString(12);
			}
		}catch(SQLException e){
			// Put error in msg string
			msg = stack2string(e);
			return null;
		}
		// Return home object
		return h;
	}
	
	// Get a list of home objects of all the Houses in the database
	public static ArrayList<home> getAllHomes()
	{
		ArrayList<home> rtnList = new ArrayList<home>();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select * from homes");
			while(rset.next())
			{
				home h = new home();
				h.id =  rset.getInt(1);
				h.address = rset.getString(2);
				h.city = rset.getString(3);
				h.state = rset.getString(4);
				h.zipcode = rset.getInt(5);
				h.sqft = rset.getInt(6);
				h.numFloors = rset.getInt(7);
				h.numBedrooms = rset.getInt(8);
				h.numBathrooms = rset.getInt(9);
				h.landSize = rset.getInt(10);
				h.year = rset.getInt(11);
				h.homeClass = rset.getString(12);
				rtnList.add(h);
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return null;
		}
		return rtnList;
	}

	// Add house to market by homeID
	public static boolean addMarket(int HomeID, int AgentID,
			int Price, String startDate)
	{
		String query = "insert into market values(" + HomeID + "," +
				AgentID + "," + Price + ", '"+startDate+"')";
		try{
			Statement stmt = conn.createStatement();
			stmt.executeQuery(query);
			stmt.close();
		}catch(SQLException e){
			msg = query + "\n\n\n" +stack2string(e);
			return false;
		}
		return true;
	}

	// Add new house to database
	public static boolean newHome(String address, String city,
			String state, int zipcode,
			int floorSpace, int floors, int bedrooms, 
			int bathrooms,int landSize, int year, String homeClass)
	{
		String query = "insert into homes values (HomeID.NEXTVAL,'" +
				address + "','" + city + "','" + state + "'," + 
				zipcode + "," + floorSpace + "," + 
				floors + "," + bedrooms + "," + 
				bathrooms + "," + landSize + "," + 
				year + ",'"+ homeClass +"')";
		try{
			Statement stmt = conn.createStatement();
			stmt.executeQuery(query);
			stmt.close();
		}catch(SQLException e){
			msg = query + "\n\n\n" +stack2string(e);
			return false;
		}
		return true;
	}

	// Get the max homeid from the homes table
	public static int getLastHomeID()
	{
		int id = -1;
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("select max(homeID) from homes");
			while(rset.next())
			{
				id = rset.getInt(1);
			}
			stmt.close();
		}catch(SQLException e){
			msg = stack2string(e);
		}
		return id;
	}
	
	// Delete home from database
	public static boolean delHome(int homeID)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.execute("delete from market where homeID = " + homeID);
			stmt.execute("delete from appliances where homeID = " + homeID);
			stmt.execute("delete from homes where homeID = " + homeID);
			stmt.close();
			return true;
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
	}	

	// Add agent to database
	public static boolean addAgent(String agentName)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.execute("insert into agents values (AgentID.NEXTVAL,'"+agentName+"')");
			stmt.close();
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
		return true;

	}

	// Delete agent from database
	public static boolean delAgent(int AgentID)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.execute("delete from agents where AgentID = " + AgentID);
			stmt.execute("delete from agents where AgentID = " + AgentID);
			stmt.close();
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
		return true;
	}	

	// delete home / appliance relation 
	public static boolean delAppliance(int appID)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.execute("delete from Appliances where ApplianceID = " + appID);
			stmt.close();
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
		return true;
	}	

	// Add appliance to house
	public static boolean addAppliance(String HomeID, String appType, String Maker)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.execute("insert into Appliances(ApplianceID, HomeID, appType, Maker) values (ApplianceID.NEXTVAL,'"+HomeID+"','"+appType+"','"+Maker+"')");
			stmt.close();
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
		return true;

	}

	// Get a list of appliance for a home 
	public static ArrayList<String> getAppliancesForHouse(int homeid)
	{
		ArrayList<String> rtnList = new ArrayList<String>();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select * from appliances where HOMEID =" + homeid);
			while(rset.next())
			{
				String a = new String();
				a =  rset.getString(4)+ " " + rset.getString(3);
				rtnList.add(a);
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return null;
		}
		return rtnList;
	}

	// Check if a house is on the market
	public static boolean isHouseOnMarket(int homeid)
	{
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select count(*) from market "+
					"where HOMEID =" + homeid);
			while(rset.next())
			{
				int ct = rset.getInt(1);
				if(ct > 0)
					return true;
				else 
					return false;
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
		return false;
	}

	// Check if agent is assigned to a house on the market
	public static boolean isAgentAssigned(int agentID)
	{
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select count(*) from market "+
					"where AGENTID =" + agentID);
			while(rset.next())
			{
				int ct = rset.getInt(1);
				if(ct > 0)
					return true;
				else 
					return false;
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return false;
		}
		return false;
	}

	// Get a list of all homes on the market
	public static ArrayList<home> getAllHomesOnMarket()
	{
		ArrayList<home> rtnList = new ArrayList<home>();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select * from homes "+
					"inner join market on "+
					"homes.homeID = market.homeID");
			while(rset.next())
			{
				home h = new home();
				h.id =  rset.getInt(1);
				h.address = rset.getString(2);
				h.city = rset.getString(3);
				h.state = rset.getString(4);
				h.zipcode = rset.getInt(5);
				h.sqft = rset.getInt(6);
				h.numFloors = rset.getInt(7);
				h.numBedrooms = rset.getInt(8);
				h.numBathrooms = rset.getInt(9);
				h.landSize = rset.getInt(10);
				h.year = rset.getInt(11);
				h.homeClass = rset.getString(12);
				h.agent = getAgentByID(rset.getInt(14));
				h.price = rset.getString(15);
				h.dateOnMarket = rset.getString(16);
				rtnList.add(h);
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return null;
		}
		return rtnList;
	}

	// Get a list of all agents in the database
	public static ArrayList<String> getAgents()
	{
		ArrayList<String> rtnList = new ArrayList<String>();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select * from agents");
			while(rset.next())
			{
				String a = new String();
				// 1 = ID
				a =  rset.getString(1);
				rtnList.add(a);
				a =  rset.getString(2);
				rtnList.add(a);
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return null;
		}
		return rtnList;
	}	

	// Get agent info by agentid
	public static String getAgentByID(int id)
	{
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(
					"Select agentname from agents where agentid = " + id);
			while(rset.next())
			{
				return(rset.getString(1));
			}
		}catch(SQLException e){
			msg = stack2string(e);
			return null;
		}
		return null;
	}

	// Convert stacktrace to string for displaying in error messages
	public static String stack2string(Exception e) {
		try {
			StringWriter sw = new StringWriter();
			PrintWriter pw = new PrintWriter(sw);
			e.printStackTrace(pw);
			return "------\r\n" + sw.toString() + "------\r\n";
  		}
  		catch(Exception e2) {
    			return "bad stack2string";
  		}
 	}

}
